/**    
 * 文件名：ExcelHelper.java       
 * 版本信息：    
 * 日期：2016年5月10日    
 * Copyright AICAI 2016     
 * 版权所有    
 */
package com.yantong.utils.excel;

import com.yantong.utils.DateUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelHelper {
	/** logger */
	static Logger logger = LoggerFactory.getLogger(ExcelHelper.class);
	private static final short LENGTH_DATE = (short) (DateUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss").length()
			* 260);

	private static final short LENGTH_STRING = (short) (10 * 260);

	/**
	 * 
	 * @param response
	 *            response
	 * @param fileName
	 *            fileName
	 * @param sheetName
	 *            sheetName
	 * @param data
	 *            data
	 * @throws IOException
	 *             {@link IOException}
	 */
	public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, List<List<Object>> data) throws IOException {
		if (fileName == null) {
			fileName = "export-";
		}
		fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
		OutputStream out = null;
		try {
			out = response.getOutputStream();
			response.reset();
			response.setHeader("Content-Type", "application/vnd.ms-excel; charset=GBK");
			response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");

			ExcelVo vo = new ExcelVo();
			List<Object[]> rows = new ArrayList<>();
			for (List<Object> l : data) {
				rows.add(l.toArray());
			}
			vo.setRows(rows);
			vo.setSheetName(sheetName);

			Workbook wb = createExcleFile(vo);
			wb.write(out);
			out.flush();
			response.flushBuffer();
		} catch (Exception e) {
			logger.error("下载excel失败,e=" + e.getMessage());
		} finally {
			if (out != null) {
				out.close();
			}
		}
	}
	public static HSSFWorkbook createExcleFile(ExcelVo vo) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(); // 产生工作表对象
		String sheetName = vo.getSheetName();
		if (StringUtils.isEmpty(sheetName)) {
			sheetName = "firstSheet";
		}
		workbook.setSheetName(0, sheetName);

		int s = 0;
		if (vo.getFields() != null && vo.getFields().length > 0) {
			s = 1;
			// 产生一行
			HSSFRow row = sheet.createRow(0);
			for (int i = 0; i < vo.getFields().length; i++) {
				String field = vo.getFields()[i];
				HSSFCell cell0 = row.createCell(i);
				cell0.setCellValue(field);
				cell0.setCellStyle(getFieldNameStyle(workbook));
			}
		}
		if (vo.getRows() != null && vo.getRows().size() > 0) {
			HSSFCellStyle dateStyle = getDateStyle(workbook);
			int l = vo.getRows().size();
			int x = 0;
			boolean cellSizeInited = false;

			for (int i = s; i < s + l; i++) {
				if ((i % 500) == 0) {
					System.err.print(".");
				}
				Object[] os = vo.getRows().get(x);
				x++;
				HSSFRow row = sheet.createRow(i);
				for (int j = 0; j < os.length; j++) {
					Object o = os[j];
					if (o == null) {
						o = "";
					}
					HSSFCell cell = row.createCell(j);
					if (o instanceof Integer || o instanceof Float || o instanceof Double || o instanceof BigDecimal || o instanceof Long) {
//						cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
						cell.setCellType(CellType.NUMERIC);
						Double d = new Double(o.toString());
						cell.setCellValue(d);
					} else if (o instanceof String) {
						cell.setCellType(CellType.STRING);
//						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue((String) o);
						if (!cellSizeInited) {
							sheet.setColumnWidth(j, LENGTH_STRING);
						}

					} else if (o instanceof Date) {
						Date d = (Date) o;
						cell.setCellStyle(dateStyle);
						cell.setCellValue(d);
						if (!cellSizeInited) {
							sheet.setColumnWidth(j, LENGTH_DATE);
						}
					} else {
						cell.setCellType(CellType.STRING);
//						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(o.toString());
						if (!cellSizeInited) {
							sheet.setColumnWidth(j, LENGTH_STRING);
						}

					}
				}
				if (!cellSizeInited) {
					cellSizeInited = true;
				}
			}
		}
		return workbook;
	}

	private static HSSFCellStyle getFieldNameStyle(HSSFWorkbook workbook) {
		HSSFCellStyle cs = workbook.createCellStyle();
		HSSFFont f = workbook.createFont();
		f.setColor((short) HSSFColor.BLUE.index);
		f.setBold(true);
//		f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cs.setFont(f);
		cs.setBorderBottom(BorderStyle.THIN);
//		cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		return cs;
	}

	private static HSSFCellStyle getDateStyle(HSSFWorkbook workbook) {
		HSSFCellStyle cellStyle = workbook.createCellStyle();// 建立新的cell样式
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));// 设置cell样式为定制的日期格式
		return cellStyle;
	}
}
